CREATE OR REPLACE PACKAGE LotNaming IS

--===============================================================
-- Type declarations
--===============================================================

--===============================================================
-- Function and Procedure declarations
--===============================================================
FUNCTION StartLotName (LotType IN VARCHAR2
                       , LotCategory IN VARCHAR2)
RETURN VARCHAR2
;
FUNCTION StartMaterialName  (LotType IN VARCHAR2
                             , LotCategory IN VARCHAR2)
RETURN VARCHAR2
;
FUNCTION SplitLotName (ParentLotName VARCHAR2)
RETURN VARCHAR2
;
FUNCTION VerifyLotName (LotName IN VARCHAR2
                        , Device IN VARCHAR2)
RETURN VARCHAR2
;
PROCEDURE GetWorkWeek (WorkWeek OUT VARCHAR2)
;
--===========================================================================
-- End of package specification
--===========================================================================
END;
/
CREATE OR REPLACE PACKAGE BODY LotNaming AS

--=====================
-- StartLotName
--=====================
FUNCTION StartLotName (LotType IN VARCHAR2
                       , LotCategory IN VARCHAR2)
RETURN VARCHAR2 IS
    --=====================
    -- Input parameter variables
    --=====================

    --=====================
    -- Cursor Declarations
    --=====================

    --=====================
    -- Local variables
    --=====================
    vContainerName VARCHAR2(40);
    vYear VARCHAR2(40);
    vWorkWeek VARCHAR2(40);
    vSequence VARCHAR2(40);
    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Set Year
    --=====================
    SELECT
        UPPER(LPAD(LTRIM(TO_CHAR(TO_CHAR(SYSDATE, 'yy'), 'xx')), 2, 0))
    INTO
        vYear
    FROM
        DUAL;
    --=====================
    -- Set Work Week
    --=====================
    GetWorkWeek (vWorkWeek);
    --=====================
    -- Set Sequence
    --=====================
    SELECT
        UPPER(LPAD(LTRIM(TO_CHAR(S_SetContainerName.NEXTVAL, 'xxxx')), 4, 0))
    INTO
        vSequence
    FROM
        DUAL;
    --=====================
    -- Set the Container Name
    --=====================
    vContainerName := LotType || vYear || vWorkWeek || LotCategory || vSequence;
    --=====================
    -- Return the container name
    --=====================
    RETURN ('0 ' || vContainerName);
EXCEPTION
    WHEN eExit THEN
        RETURN ('LotNaming.StartLotName->Exited prematurely');
    WHEN eAppError THEN
        RETURN (REPLACE(SQLERRM, 'ORA-20005: '));
    WHEN OTHERS THEN
        RETURN ('LotNaming.StartLotName->' || SQLERRM);
END StartLotName;

--=====================
-- StartMaterialName
--=====================
FUNCTION StartMaterialName (LotType IN VARCHAR2
                            , LotCategory IN VARCHAR2)
RETURN VARCHAR2 IS
    --=====================
    -- Input parameter variables
    --=====================

    --=====================
    -- Cursor Declarations
    --=====================

    --=====================
    -- Local variables
    --=====================
    vMaterialLotName VARCHAR2(40);
    vYear VARCHAR2(40);
    vWorkWeek VARCHAR2(40);
    vSequence VARCHAR2(40);
    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Set Year
    --=====================
    SELECT
        UPPER(LPAD(LTRIM(TO_CHAR(TO_CHAR(SYSDATE, 'yy'), 'xx')), 2, 0))
    INTO
        vYear
    FROM
        DUAL;
    --=====================
    -- Set Work Week
    --=====================
    GetWorkWeek (vWorkWeek);
    --=====================
    -- Set Sequence
    --=====================
    SELECT
        UPPER(LPAD(LTRIM(TO_CHAR(S_SetMaterialLotName.NEXTVAL, 'xxxx')), 4, 0))
    INTO
        vSequence
    FROM
        DUAL;
    --=====================
    -- Set the Container Name
    --=====================
    vMaterialLotName := LotType || vYear || vWorkWeek || LotCategory || vSequence;
     --=====================
    -- Return the Material name
    --=====================
   RETURN ('0 ' || vMaterialLotName);
EXCEPTION
    WHEN eExit THEN
        RETURN ('LotNaming.StartMaterialName->Exited prematurely');
    WHEN eAppError THEN
        RETURN (REPLACE(SQLERRM, 'ORA-20005: '));
    WHEN OTHERS THEN
        RETURN ('LotNaming.StartMaterialName->' || SQLERRM);
END StartMaterialName;

--=====================
-- SplitLotName
--=====================
FUNCTION SplitLotName (ParentLotName VARCHAR2)
RETURN VARCHAR2 IS
    --=====================
    -- Input parameter variables
    --=====================
    vParentLotName Container.ContainerName%TYPE := ParentLotName;
    --=====================
    -- Cursor Declarations
    --=====================

    --=====================
    -- Local variables
    --=====================
    vSplitLotName Container.ContainerName%TYPE;
    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Set the child lot name
    --=====================
    SELECT
        vParentLotName || '-' || TRIM(TO_CHAR(ChildCount + 1, '000'))
    INTO
        vSplitLotName
    FROM
        Container
    WHERE
        ContainerName = vParentLotName;
    --=====================
    -- Return success
    --=====================
    RETURN ('0 ' || vSplitLotName);
EXCEPTION
    WHEN eExit THEN
        RETURN ('LotNaming.SplitLotName->Exited prematurely');
    WHEN eAppError THEN
        RETURN (REPLACE(SQLERRM, 'ORA-20005: '));
    WHEN OTHERS THEN
        RETURN ('LotNaming.SplitLotName->' || SQLERRM);
END SplitLotName;

--=====================
-- VerifyLotName
--=====================
FUNCTION VerifyLotName (LotName IN VARCHAR2
                       , Device IN VARCHAR2)
RETURN VARCHAR2 IS
    --=====================
    -- Input parameter variables
    --=====================
    vLotName VARCHAR2(100) := LotName;
    vDevice VARCHAR2(100) := Device;

    --=====================
    -- Cursor Declarations
    --=====================
    --=====================
    -- Retrieve Lot Naming Setup
    --=====================
    CURSOR curSetup (IN_Device A_LotNamingSetup.Device%TYPE) IS
    SELECT
        A.*
    FROM
        A_LotNamingSetup A
    WHERE
        A.Device = IN_Device
    ;

    --=====================
    -- Local variables
    --=====================
    rSetup curSetup%ROWTYPE;
    nMaxLength int := 0;

    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Check Lot Naming Setup
    --=====================
    OPEN curSetup (vDevice);
    FETCH curSetup INTO rSetup;

    IF curSetup%FOUND THEN
        CLOSE curSetup;

        --=====================
        -- Check Maximum Characters
        --=====================
        nMaxLength := TO_NUMBER (rSetup.MaxLength);

        IF LENGTH(vLotName) > nMaxLength THEN
            RAISE_APPLICATION_ERROR (-20005, 'LotNamingHandler_E0001 ' || LotName || ' ' || nMaxLength);

        END IF;
    END IF;

    --=====================
    -- Return success
    --=====================
    RETURN ('0');
EXCEPTION
    WHEN eExit THEN
        RETURN ('LotNaming.VerifyLotName->Exited prematurely');
    WHEN eAppError THEN
        RETURN (REPLACE(SQLERRM, 'ORA-20005: '));
    WHEN OTHERS THEN
        RETURN ('LotNaming.VerifyLotName->' || SQLERRM);
END VerifyLotName;

--=====================
-- GetWorkWeek
--=====================
PROCEDURE GetWorkWeek (WorkWeek OUT VARCHAR2)
IS
    --=====================
    -- Input parameter variables
    --=====================

    --=====================
    -- Cursor Declarations
    --=====================
    CURSOR curWorkWeek IS
    SELECT
        TRIM(TO_CHAR(B.WorkWeek, '00')) CalendarWorkWeek
    FROM
        A_Calendar A
        , A_CalendarWorkWeeks B
    WHERE
        A.CalendarId = B.CalendarId
        AND SysDate >= B.StartDate
        AND SysDate < B.EndDate
    ORDER BY
        A.CalendarName
    ;
    --=====================
    -- Local variables
    --=====================
    rWorkWeek curWorkWeek%ROWTYPE;
    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Get the Date Code Sequence
    --=====================
    OPEN curWorkWeek;
    FETCH curWorkWeek INTO rWorkWeek;
    IF curWorkWeek%FOUND THEN
        CLOSE curWorkWeek;
        WorkWeek := rWorkWeek.CalendarWorkWeek;
    END IF;
EXCEPTION
    WHEN eExit THEN
        ROLLBACK;
    WHEN eAppError THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'LotNaming.GetWorkWeek->' || SQLERRM);
END GetWorkWeek;

--===========================================================================
-- End of package body
--===========================================================================
END;
/
